﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_SMS_GetHistory]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_SMS_GetHistory];
GO
CREATE PROCEDURE [dbo].[sproc_SMS_GetHistory]
    @Sender nvarchar(50),
    @Receiver nvarchar(50)
/*

===================================================
功能:    获取历史记录
参数:
    @Sender 发送者
    @Receiver 接收者
        
返回值:
    记录集
===================================================

*/
AS

SET NOCOUNT ON

--判断是否有未读新的短消息
SELECT  (SELECT TOP 1 RealName 
                    FROM 
                        UDS_Staff 
                    WHERE     UDS_Staff.Staff_Name=@Sender)
            AS Sender,Content,SendTime
            FROM 
                UDS_SMS_Msg m,
                UDS_SMS_Receiver r 
            WHERE    
                r.Receiver = @Receiver
                and m.ID=r.MsgID 
                and sender = @Sender 
UNION 

SELECT   (SELECT TOP 1 RealName 
                    FROM 
                        UDS_Staff 
                    WHERE     UDS_Staff.Staff_Name=@Receiver)
            AS Sender,Content,SendTime
            FROM 
                UDS_SMS_Msg m,
                UDS_SMS_Receiver r 
            WHERE    
                r.Receiver = @Sender
                and m.ID=r.MsgID 
                and sender = @Receiver
                
                ORDER BY
                    sendtime
                DESC
            
SET NOCOUNT OFF